package com.rdc.example.dao.impl;

import com.rdc.example.dao.PlayListDao;
import com.rdc.example.entity.PlayList;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static com.rdc.example.util.DBUtil.closeResources;
import static com.rdc.example.util.DBUtil.getConnection;

public class PlayListDaoImpl implements PlayListDao {

    /*public static void main(String[] args) throws Exception {
        PlayListDaoImpl playListDap = new PlayListDaoImpl();
        PlayList playList = new PlayList();
        playList.setUserId(1);
        playList.setName("yes");
        playList.setIsLike(1);
        playListDap.insert(playList);
    }*/

    @Override
    public boolean insert(PlayList playList) throws Exception {
        // 进行数据库插入操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //获取连接
            conn = getConnection();
            String sql = "insert into `play_list` (user_id, name, cover_path, is_like, is_public) values (?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            //设值
            setPlayList(pstmt, playList);
            //按照影响行数row返回结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean deleteById(Integer playListId) throws Exception {
        // 进行数据库删除操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //获取连接
            conn = getConnection();
            String sql = "delete from `play_list` where id=? ";
            pstmt = conn.prepareStatement(sql);
            //设值
            pstmt.setLong(1, playListId);
            // 不检查影响行数
            pstmt.executeUpdate();
            // 只要SQL执行无异常，均返回true
            return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean update(PlayList playList) throws Exception {
        // 进行数据库更新操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //获取连接
            conn = getConnection();
            String sql = "update `play_list` set user_id =?,name=?,cover_path=?,is_like=?,is_public=? where id=?";
            pstmt = conn.prepareStatement(sql);
            //设值
            setPlayList(pstmt, playList);
            pstmt.setInt(6, playList.getId());
            int row = pstmt.executeUpdate();
            //按照影响行数row返回结果
            return row > 0;
        }  catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public PlayList getRandomPlayList() throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        PlayList playList = null;
        try {
            conn = getConnection();
            // 随机查询 SQL
            // 最大 id *（0 - 1）随机数，找大于等于这个数的 id
            String sql = "select * from play_list where id >= (select floor(rand() * (select max(id) from play_list))) order by id limit 1";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                playList = getPlayList(rs);
            }
        } catch (SQLException e) {
            throw new RuntimeException("随机歌单查询失败", e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return playList;
    }

    @Override
    public Integer getRedHeartPlayListIdByUserId(Integer userId) throws Exception {
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Integer playListId = null;
        try {
            // 查询用户唯一爱心歌单的 id
            String sql = "select id from `play_list` where user_id = ? and is_like = 1";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            // 封装 playListId 数据
            if (rs.next()) {
                playListId = rs.getInt("id");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return playListId;
    }

    @Override
    public PlayList getPlayListById(Integer id) throws Exception {
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        PlayList playList = null;
        try {
            String sql = "select * from `play_list` where id=?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            // 封装 playList 数据
            if (rs.next()) {
                playList = getPlayList(rs);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return playList;
    }

    @Override
    public Integer countPlayListByUserId(Integer userId) throws Exception {
        // 进行数据库查询操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = getConnection();
            String sql = "select count(*) from `play_list` where user_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }

    @Override
    public List<PlayList> getPlayListsByPageAndUserId(Integer userId, Integer pageBegin, Integer pageSize) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PlayList> playListList = new ArrayList<>();
        try {
            String sql = "select * from `play_list` where user_id=? limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                PlayList playList = getPlayList(rs);
                playListList.add(playList);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return playListList;
    }

    @Override
    public List<PlayList> searchPlaylistsByTitle(String keyword, Integer pageBegin, Integer pageSize) throws Exception {
        List<PlayList> playlistList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "select * from `play_list` where name like ? limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                playlistList.add(getPlayList(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return playlistList;
    }

    @Override
    public Integer countSearchPlaylistsByTitle(String keyword) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from `play_list` where name like ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }








    private void setPlayList(PreparedStatement pstmt, PlayList playList) throws Exception {
        pstmt.setInt(1, playList.getUserId());
        pstmt.setString(2, playList.getName());
        pstmt.setString(3, playList.getCoverPath()!=null?playList.getCoverPath():null);
        //默认0，不是红心歌单
        pstmt.setInt(4, playList.getIsLike()!=null?playList.getIsLike():0);
        //默认1 公开
        pstmt.setInt(5, playList.getIsPublic()!= null ? playList.getIsPublic() : 1);
    }

    private PlayList getPlayList(ResultSet rs) throws Exception {
        PlayList playList = new PlayList();
        playList.setId(rs.getInt("id"));
        playList.setName(rs.getString("name"));
        playList.setCoverPath(rs.getString("cover_path"));
        playList.setIsLike(rs.getInt("is_like"));
        playList.setIsPublic(rs.getInt("is_public"));
        playList.setUserId(rs.getInt("user_id"));
        playList.setCoverPath(rs.getString("cover_path"));
        return playList;
    }
}
